﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace BanHangCshap.Class
{
    class PhieuThuChi:Database
    {
        #region ***** Fields & Properties *****
        private string _mathuchi;
        public string mathuchi
        {
            get
            {
                return _mathuchi;
            }
            set
            {
                _mathuchi = value;
            }
        }
        private string _ngaythuchi;
        public string ngaythuchi
        {
            get
            {
                return _ngaythuchi;
            }
            set
            {
                _ngaythuchi = value;
            }
        }
        private string _makhncc;
        public string makhncc
        {
            get
            {
                return _makhncc;
            }
            set
            {
                _makhncc = value;
            }
        }
        private string _loaithuchi;
        public string loaithuchi
        {
            get
            {
                return _loaithuchi;
            }
            set
            {
                _loaithuchi = value;
            }
        }
        private double _sotienso;
        public double sotienso
        {
            get
            {
                return _sotienso;
            }
            set
            {
                _sotienso = value;
            }
        }
        private string _sotienchu;
        public string sotienchu
        {
            get
            {
                return _sotienchu;
            }
            set
            {
                _sotienchu = value;
            }
        }
        private double _conno;
        public double conno
        {
            get
            {
                return _conno;
            }
            set
            {
                _conno = value;
            }
        }
        private int _manoidungthuchi;
        public int manoidungthuchi
        {
            get
            {
                return _manoidungthuchi;
            }
            set
            {
                _manoidungthuchi = value;
            }
        }
        private int _maquy;
        public int maquy
        {
            get
            {
                return _maquy;
            }
            set
            {
                _maquy = value;
            }
        }
        private string _ghichutc;
        public string ghichutc
        {
            get
            {
                return _ghichutc;
            }
            set
            {
                _ghichutc = value;
            }
        }
        private string _tendn;
        public string tendn
        {
            get
            {
                return _tendn;
            }
            set
            {
                _tendn = value;
            }
        }
        private int _id;
        public int id
        {
            get
            {
                return _id;
            }
            set
            {
                _id = value;
            }
        }
        #endregion

        #region ***** Init Methods *****
        public PhieuThuChi()
        {
        }
        public PhieuThuChi(string mathuchi)
        {
            this.mathuchi = mathuchi;
        }
        //public PhieuThuChi(string mathuchi, string ngaythuchi, int makhncc, string loaithuchi, double sotienso, string sotienchu, int manoidungthuchi, int mahinhthuctt, string tendn)
        //{
        //    this.mathuchi = mathuchi;
        //    this.ngaythuchi = ngaythuchi;
        //    this.makhncc = makhncc;
        //    this.loaithuchi = loaithuchi;
        //    this.sotienso = sotienso;
        //    this.sotienchu = sotienchu;
        //    this.manoidungthuchi = manoidungthuchi;
        //    this.mahinhthuctt = mahinhthuctt;
        //    this.tendn = tendn;
        //}
        #endregion
        public double laySoPhieuThuChiTrongNgay(string ngay, string loaithuchi)
        {
            string sql = "SELECT max(convert(int,substring(mathuchi,12,len(mathuchi)-10))) FROM PhieuThuChi WHERE convert(varchar(10),ngaythuchi,103)='" + ngay + "' and loaithuchi = '" + loaithuchi + "'";
            return getNumberValue(sql);
        }
        public Boolean themThuChi()
        {
            string sql = "insert into PhieuThuchi(mathuchi,ngaythuchi,makhncc,loaithuchi,sotienso,sotienchu,manoidungthuchi,ghichutc,maquy,tendn,id) ";
            sql += "values ('" + mathuchi + "','" + ngaythuchi + "','" + makhncc + "',N'" + loaithuchi + "'," + sotienso + ",N'" + sotienchu + "'," + manoidungthuchi + ",N'" + ghichutc + "'," + maquy + ",'" + tendn + "'," + id + ")";
            return executeQueryTrans(sql);
        }
        public Boolean suaThuChi()
        {
            string sql = "update PhieuThuChi set ngaythuchi='" + ngaythuchi + "',makhncc='" + makhncc + "',loaithuchi=N'" + loaithuchi + "',sotienso=" + sotienso + ",sotienchu=N'" + sotienchu + "', ";
            sql += "manoidungthuchi=" + manoidungthuchi + ",ghichutc = N'" + ghichutc + "',maquy = " + maquy + ",tendn='" + tendn + "', id = " + id + " where mathuchi='" + mathuchi + "' ";
           
            return executeQueryTrans(sql);
        }
        public Boolean xoaThuChi(string mathuchi)
        {
            string sql = "delete PhieuThuChi where mathuchi = '" + mathuchi + "'";
            return executeQuery(sql);

           
        }
        public DataTable layThongTinPhieuThuChi(string mathuchi)
        {
            string sql = "select * from v_PhieuThuChi where mathuchi = '" + mathuchi + "'";
            return getDataTable(sql);
        }
        public DataTable inPhieuThuChi(string thongtindoanhnghiep, string mathuchi)
        {
            string sql = "select isnull(N'" + thongtindoanhnghiep + "','') as thongtindoanhnghiep, case when loaithuchi = 'Thu' then N'PHIẾU THU' else N'PHIẾU CHI' end as tieudereport,case when loaithuchi = 'Thu' then N'Người nộp: ' else N'Người nhận: ' end as nopnhan,mathuchi,convert(varchar(10),ngaythuchi,103) as ngaythuchi,tenkhncc,diachikhncc,tennoidungthuchi, sotienso, sotienchu,ghichutc ";
            sql += "from v_PhieuThuChi where  mathuchi = '" + mathuchi + "'";
            return getDataTable(sql);
        }
        public int layIDPhieuThuChiMax()
        {
            string sql = "select max(id) from PhieuThuChi";
            return (int)getNumberValue(sql);
        }
        public DataTable layDSThuChi(string loaithuchi)
        {
            string sql = "select * from v_PhieuThuChi where loaithuchi = '" + loaithuchi + "' order by ngaythuchi desc";
            return getDataTable(sql);
        }
        public DataTable timKiemThuChi(string loaithuchi, string tungay, string denngay)
        {
           
            string sql = "select loaithuchi,mathuchi ,ngaythuchi,manoidungthuchi,tennoidungthuchi,nocu,sotienso,sotienchu,makhncc,tenkhncc,maquy,tenquy,tendn,id from v_PhieuThuChi where  loaithuchi = '" + loaithuchi + "'  ";
            // sql += " convert(varchar(10),ngaythuchi,103) = '" + ngay + "'    ";
            if (tungay.Trim() != "" && tungay.Trim() != "")
            {
                sql += "and  convert(DateTime,ngaythuchi,103) >= convert(DateTime,'" + tungay + "',103)  and  convert(DateTime,ngaythuchi,103) <= convert(DateTime,'" + denngay + "',103)   ";
            }
           
            sql += "order by ngaythuchi desc";
            return getDataTable(sql);
        }
        public DataTable timKiemPhieuThuChi(string loaithuchi, string tungay,string denngay, string makhncc,string tendn)
        {
            //string sql = "select loaithuchi,mathuchi ,ngaythuchi,a.manoidungthuchi,tennoidungthuchi,sotienso,sotienchu,a.makhncc,tenkhncc,a.maquy,tenquy,tendn,id from PhieuThuChi a, KhachHangNCC b, NoiDungThuChi c,Quy d where  loaithuchi = '" + loaithuchi + "' and ";
            //sql += " convert(varchar(10),ngaythuchi,103) = '" + ngay + "'  and a.makhncc = b.makhncc and a.manoidungthuchi = c.manoidungthuchi and a.maquy = d.maquy  ";
            ////sql += " convert(datetime,ngaythuchi,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaythuchi,103) <= convert(datetime,'" + denngay + "',103) and a.makhncc = b.makhncc and a.manoidungthuchi = c.manoidungthuchi  ";
            //if (makhncc != "")
            //{
            //    sql += "and a.makhncc = '" + makhncc + "'";
            //}
            //if (tendn != "")
            //{
            //    sql += "and tendn = '" + tendn + "'";
            //}
            //if (manoidungthuchi != "")
            //{
            //    sql += "and a.manoidungthuchi = '" + manoidungthuchi + "'";
            //}
            //if (maquy != "")
            //{
            //    sql += "and a.maquy = '" + maquy + "'";
            //}
            //sql += "order by ngaythuchi desc";
            string sql = "select loaithuchi,mathuchi ,ngaythuchi,manoidungthuchi,tennoidungthuchi,nocu,sotienso,sotienchu,makhncc,tenkhncc,maquy,tenquy,tendn,id from v_PhieuThuChi where  loaithuchi = '" + loaithuchi + "'  ";
           // sql += " convert(varchar(10),ngaythuchi,103) = '" + ngay + "'    ";
            if (tungay.Trim() != "" && tungay.Trim() != "")
            {
                sql += "and  convert(DateTime,ngaythuchi,103) >= convert(DateTime,'" + tungay + "',103)  and  convert(DateTime,ngaythuchi,103) <= convert(DateTime,'" + denngay + "',103)   ";
            }
            if (makhncc.Trim() != "")
            {
                sql += "and makhncc = '" + makhncc + "' ";
            }
            if (tendn != "")
            {
                sql += "and tendn = '" + tendn + "' ";
            }
           
            sql += "order by ngaythuchi desc";
            return getDataTable(sql);
        }
        public DataTable layDSPhieuThuChiTrongNgay(string loaithuchi,string ngay)
        {
            string sql = "select loaithuchi,mathuchi ,ngaythuchi,manoidungthuchi,tennoidungthuchi,nocu,sotienso,sotienchu,makhncc,tenkhncc,maquy,tenquy,tendn,id from v_PhieuThuChi where  loaithuchi = '" + loaithuchi + "'  ";
            // sql += " convert(varchar(10),ngaythuchi,103) = '" + ngay + "'    ";
           
            sql += " and  convert(varchar(10),ngaythuchi,103) = '" + ngay + "' order by ngaythuchi desc   ";
            return getDataTable(sql);
            
           

            
        }
        public DataTable layDSBanHangVaThuTienTheoNgay(string tieude,string thongtindoanhnghiep, string tungay, string denngay)
        {
            string sql = "select N'" + tieude + "' as tieude,N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,N'Từ ngày: ' + '" + tungay + "' + N' đến ngày: ' + '" + denngay + "' as khoangthoigian, ";
            sql += "hoten,ngay,sochungtu,a.makhncc,tenkhncc,diengiai,nocu,tienhang,thu,conno,loai ";
            sql += "from  ";
            sql += "( ";
	            sql += "( ";
		            sql += "select distinct makhncc,tendn,ngaynhapxuat as ngay,manhapxuat as sochungtu,N'Xuất hàng bán' as diengiai,nocu,tongtienso as tienhang, thanhtoan as thu, tongnoso as conno,id,'phieuxuat' as loai ";
		            sql += "from v_PhieuNhapXuat where   loai = 4 AND   ";
                    sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)   ";
	            sql += ") ";
	            sql += "union all ";
                sql += "( ";
                sql += "select distinct makhncc,tendn,ngaynhapxuat as ngay,manhapxuat as sochungtu,N'Xuất hàng bán' as diengiai,nocu,tongtienso as tienhang, thanhtoan as thu, tongnoso as conno,id,'phieuxuatbohanghoa' as loai ";
                sql += "from v_PhieuNhapXuat where   loai = 5 AND   ";
                sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)   ";
                sql += ") ";
                sql += "union all ";
	            sql += "( ";
		            sql += "select distinct makhncc,tendn,ngaythuchi as ngay,mathuchi as sochungtu,tennoidungthuchi as diengiai,nocu,0 as tienhang,sotienso as thu,   nocu - sotienso as conno,id,'phieuthu' as loai ";
		            sql += "from v_PhieuThuChi where   loaithuchi = 'Thu' and ";
                    sql += "convert(datetime,ngaythuchi,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaythuchi,103) <= convert(datetime,'" + denngay + "',103)   ";
	            sql += ")  ";
                sql += ") a left join KhachHangNCC b on a.makhncc = b.makhncc left join NguoiDung c on a.tendn = c.tendn order by id";
                return getDataTable(sql);
            //string[] strPara = { "@tieude","@thongtindoanhnghiep", "@tungay", "@denngay" };
            //string[] strValue = { tieude,thongtindoanhnghiep,tungay, denngay };
           
            //return getDataTableSP("sp_BanHangVaThuTienTheoNgay", strPara, strValue);
        }
        public DataTable layDSThuChiTheoNgay(string tieude, string thongtindoanhnghiep, string tungay, string denngay)
        {
            string sql = "select N'" + tieude + "' as tieude,N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,N'Từ ngày: ' + '" + tungay + "' + N' đến ngày: ' + '" + denngay + "' as khoangthoigian, ";
	        sql += "hoten,ngay,sochungtu,a.makhncc,tenkhncc,diengiai,thu,chi,loai,sort ";
	        sql += "from ";
	        sql += "(	 ";
		        sql += "( ";
			        sql += "select distinct makhncc,ngaynhapxuat as ngay,manhapxuat as sochungtu,N'Xuất hàng bán' as diengiai,thanhtoan as thu,0 as chi, id,'phieuxuat' as loai,tendn,sort ";
			        sql += "from v_PhieuNhapXuat where  loai = 4 AND  thanhtoan > 0 and ";
			        sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)   ";
		        sql += ") ";
		        sql += "union all ";
                sql += "( ";
                sql += "select distinct makhncc,ngaynhapxuat as ngay,manhapxuat as sochungtu,N'Xuất hàng bán' as diengiai,thanhtoan as thu,0 as chi, id,'phieuxuatbohanghoa' as loai,tendn,sort ";
                sql += "from v_PhieuNhapXuat where  loai = 5 AND  thanhtoan > 0 and ";
                sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)   ";
                sql += ") ";
                sql += "union all ";
		        sql += "( ";
			        sql += "select distinct makhncc,ngaythuchi as ngay,mathuchi as sochungtu,tennoidungthuchi as diengiai,sotienso as thu, 0 as chi,  id,'phieuthu' as loai,tendn,sort ";
			        sql += "from v_PhieuThuChi where  loaithuchi = 'Thu' and ";
                    sql += "convert(datetime,ngaythuchi,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaythuchi,103) <= convert(datetime,'" + denngay + "',103)   ";
		        sql += ") ";

		        sql += "union all ";
		        sql += "( ";
			        sql += "select distinct makhncc,ngaynhapxuat as ngay,manhapxuat as sochungtu,N'Nhập hàng trả lại' as diengiai, 0 as  thu,thanhtoan as chi, id,'phieunhap' as loai,tendn,sort ";
			        sql += "from v_PhieuNhapXuat where  loai = 2 AND  thanhtoan > 0 and ";
                    sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)   ";
		        sql += ") ";
		        sql += "union all ";


		        sql += "( ";
			        sql += "select distinct makhncc,ngaythuchi as ngay,mathuchi as sochungtu,tennoidungthuchi as diengiai,0 as thu, sotienso as chi, id,'phieuchi' as loai,tendn,sort ";
			        sql += "from v_PhieuThuChi where  loaithuchi = 'Chi' and ";
                    sql += "convert(datetime,ngaythuchi,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaythuchi,103) <= convert(datetime,'" + denngay + "',103) ";
		        sql += ") ";
                sql += ")  a left join KhachHangNCC b on a.makhncc = b.makhncc left join NguoiDung c on a.tendn = c.tendn order by sort";
                return getDataTable(sql);
            //string[] strPara = { "@tieude", "@thongtindoanhnghiep", "@tungay", "@denngay" };
            //string[] strValue = { tieude, thongtindoanhnghiep, tungay, denngay };
          
            //return getDataTableSP("sp_ThuChiTheoNgay", strPara, strValue);
        }
        public DataTable baoCaoKetQuaKinhDoanh(string thongtindoanhnghiep, string tungay, string denngay)
        {
            string[] strPara = {  "@thongtindoanhnghiep", "@tungay", "@denngay" };
            string[] strValue = {  thongtindoanhnghiep, tungay, denngay };
            return getDataTableSP("sp_TongHopKetQuaKinhDoanh", strPara, strValue);
        }
    }
}
